---------------------------------------------------------------------
-- Inside Microsoft SQL Server 2008: T-SQL Querying (MSPress, 2009)
-- Chapter 04 - Query Tuning
-- Copyright Itzik Ben-Gan, 2009
-- All Rights Reserved
---------------------------------------------------------------------

-- create sample database and tables
USE master;
GO

IF DATABASEPROPERTYEX ('Partitioning', 'Version') > 0
BEGIN
	ALTER DATABASE Partitioning SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE Partitioning;
END

CREATE DATABASE Partitioning ON PRIMARY
  (NAME = Partitioning_dat,
  FILENAME = 'C:\MSSQL\Data\Primary\Partitioning.mdf',
  SIZE = 256MB, FILEGROWTH = 128MB)
  LOG ON
  (NAME = Partitioning_log,
  FILENAME = 'C:\MSSQL\Data\Primary\Partitioning.ldf',
  SIZE = 128MB, MAXSIZE = 4096MB, FILEGROWTH = 64MB);
GO

USE Partitioning;
GO

-- create and populate the Nums auxiliary table
SET NOCOUNT ON;

CREATE TABLE dbo.Nums
(
	n INT NOT NULL
);

DECLARE @max AS INT = 100000, @rc AS INT = 1;

INSERT INTO dbo.Nums(n) VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
  INSERT INTO dbo.Nums(n) SELECT n + @rc FROM dbo.Nums;
  SET @rc = @rc * 2;
END

INSERT INTO dbo.Nums(n)
  SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
GO

ALTER TABLE dbo.Nums ADD
  CONSTRAINT PK_Nums PRIMARY KEY(n);

-- data distribution settings
DECLARE
  @numorders   AS INT      = 100000,
  @numcusts    AS INT      = 2000,
  @numemps     AS INT      = 500,
  @numshippers AS INT      = 5,
  @numyears    AS INT      = 6,
  @startdate   AS DATETIME = '20080101';

-- create and populate the Customers table
CREATE TABLE dbo.Customers
(
  custid   CHAR(11)     NOT NULL,
  custname NVARCHAR(50) NOT NULL
);

INSERT INTO dbo.Customers(custid, custname)
  SELECT
    'C' + RIGHT('000000000' + CAST(n AS VARCHAR(10)), 10) AS custid,
	LEFT(REPLACE(CAST(NEWID() AS NVARCHAR(MAX)),'-',''), ABS(CHECKSUM(NEWID())) % 50 + 1) AS custname
  FROM dbo.Nums
  WHERE n <= @numcusts;

ALTER TABLE dbo.Customers ADD
  CONSTRAINT PK_Customers PRIMARY KEY(custid);

-- create and populate the Employees table
CREATE TABLE dbo.Employees
(
  empid     INT          NOT NULL,
  firstname NVARCHAR(25) NOT NULL,
  lastname  NVARCHAR(25) NOT NULL
);

INSERT INTO dbo.Employees(empid, firstname, lastname)
  SELECT
	n AS empid,
    LEFT(REPLACE(CAST(NEWID() AS NVARCHAR(MAX)),'-',''), ABS(CHECKSUM(NEWID())) % 25 + 1) AS firstname,
	LEFT(REPLACE(CAST(NEWID() AS NVARCHAR(MAX)),'-',''), ABS(CHECKSUM(NEWID())) % 25 + 1) AS lastname
  FROM dbo.Nums
  WHERE n <= @numemps;

ALTER TABLE dbo.Employees ADD
  CONSTRAINT PK_Employees PRIMARY KEY(empid);

-- create and populate the Shippers table
CREATE TABLE dbo.Shippers
(
  shipperid   VARCHAR(5)   NOT NULL,
  shippername NVARCHAR(50) NOT NULL
);

INSERT INTO dbo.Shippers(shipperid, shippername)
  SELECT shipperid,
  LEFT(REPLACE(CAST(NEWID() AS NVARCHAR(MAX)),'-',''), ABS(CHECKSUM(NEWID())) % 50 + 1) AS shippername
  FROM (SELECT CHAR(ASCII('A') - 2 + 2 * n) AS shipperid
        FROM dbo.Nums
        WHERE n <= @numshippers) AS D;

ALTER TABLE dbo.Shippers ADD
  CONSTRAINT PK_Shippers PRIMARY KEY(shipperid);

-- create and populate the Orders table
CREATE TABLE dbo.Orders
(
  orderid   INT        NOT NULL,
  custid    CHAR(11)   NOT NULL,
  empid     INT        NOT NULL,
  shipperid VARCHAR(5) NOT NULL,
  orderdate DATETIME   NOT NULL
); -- WITH (DATA_COMPRESSION = PAGE)

INSERT INTO dbo.Orders(orderid, custid, empid, shipperid, orderdate)
  SELECT n AS orderid,
    'C' + RIGHT('000000000'
            + CAST(
                1 + ABS(CHECKSUM(NEWID())) % @numcusts
                AS VARCHAR(10)), 10) AS custid,
    1 + ABS(CHECKSUM(NEWID())) % @numemps AS empid,
    CHAR(ASCII('A') - 2
           + 2 * (1 + ABS(CHECKSUM(NEWID())) % @numshippers)) AS shipperid,
      DATEADD(day, n / (@numorders / (@numyears * 365.25)), @startdate)
        -- late arrival with earlier date
        - CASE WHEN n % 10 = 0
            THEN 1 + ABS(CHECKSUM(NEWID())) % 30
            ELSE 0 
          END AS orderdate
  FROM dbo.Nums
  WHERE n <= @numorders
  ORDER BY CHECKSUM(NEWID());

CREATE CLUSTERED INDEX CX_Orders_OrderDate ON dbo.Orders
(
	orderdate
);

CREATE NONCLUSTERED INDEX IX_Orders_ShipperID_OrderDate ON dbo.Orders
(
	shipperid, orderdate
)
INCLUDE(custid);

CREATE UNIQUE INDEX UX_Orders_OrderDate_OrderID ON dbo.Orders
(
	orderdate, orderid
)
INCLUDE(custid, empid);

ALTER TABLE dbo.Orders ADD
  CONSTRAINT PK_Orders PRIMARY KEY NONCLUSTERED(orderid),
  CONSTRAINT FK_Orders_Customers
    FOREIGN KEY(custid)    REFERENCES dbo.Customers(custid),
  CONSTRAINT FK_Orders_Employees
    FOREIGN KEY(empid)     REFERENCES dbo.Employees(empid),
  CONSTRAINT FK_Orders_Shippers
    FOREIGN KEY(shipperid) REFERENCES dbo.Shippers(shipperid);
GO